using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using HCM_maps_website.Models;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace HCM_maps_website.Process.DAL
{
    public class UserDAO
    {
        private MapDataDataContext dbContext = new MapDataDataContext();
        public void AddPlace(int type,string FullName,string Address,Double xLocation,Double yLocation)
        {
            var place = new Place { 
                Type = type,
                FullName = FullName,
                Address = Address,
                xLocation = xLocation,
                yLocation = yLocation
            };

            dbContext.Places.InsertOnSubmit(place);
            dbContext.SubmitChanges();
            //bool A = false;
            
            //string querySearch = "insert into [map_data].[dbo].[Place] values("+ type.ToString() + ",N'" + FullName + "',N'" + Address + "'," + xLocation.ToString() +","+yLocation.ToString()+ ",'','','','')";
            //try
            //{
            //    DataProvider.ExecuteQuery(querySearch);
            //    return true;
            //}
            //catch (Exception e)
            //{
            //    return false;
            //}
            //return A;
        }
        public bool SavePlace(int IDUser,int IDPlace,DateTime DateCreate,DateTime LastAcess)
        {
            var userPlace = new UserPlace { 
                UserID = IDUser,
                PlaceID =IDPlace,
                DateCreated = DateCreate,
                LastAcessDate = LastAcess
            };
            // kiem tra da ton tai chua?
            var check = dbContext.UserPlaces.FirstOrDefault(x => x.UserID == userPlace.UserID && x.PlaceID == userPlace.PlaceID);
            if (check == null)
            {
                dbContext.UserPlaces.InsertOnSubmit(userPlace);
                dbContext.SubmitChanges();
                return true;
            }
            return false;
            //bool A=false;
            //string querySearch = "insert into [map_data].[dbo].[UserPlace] values(" + IDUser.ToString() + "," + IDPlace.ToString() + ",'" + DateCreate + "','" + LastAcess + "')";
            // try
            //{
            //    DataProvider.ExecuteQuery(querySearch);
            //    return true;
            //}
            //catch (Exception e)
            //{
            //    return false;
            //}
            //return A;
        }
        public bool SaveSearchWay(int UserID,Double xS,Double yS,Double xD,Double yD,DateTime DateCreate,DateTime lastacess)
        {
            var userRoute = new UserRoute { 
                UserID = UserID,
                xSource = xS,
                ySource = yS,
                xDestination = xD,
                yDestination = yD,
                DateCreated = DateCreate,
                LastAccessDate = lastacess
            };
            // check userroute da ton tai chua?
            var check = dbContext.UserRoutes.FirstOrDefault(x => x.UserID == userRoute.UserID 
                && x.xDestination == userRoute.xDestination && x.yDestination == userRoute.yDestination
                && x.xSource == userRoute.xSource && x.ySource == userRoute.ySource);

            //if (check == null)
            //{
            //    dbContext.UserRoutes.InsertOnSubmit(userRoute);
            //    dbContext.SubmitChanges();
            //    return true;
            //}
            //return false;

            if (check == null)
            {
                string querySearch = "insert into [map_data].[dbo].[UserRoute] values(" + UserID.ToString() + "," + xS.ToString() + "," + yS.ToString() + "," + xD.ToString() + "," + yD.ToString() + ",'" + DateCreate + "','" + lastacess + "')";
                try
                {
                    DataProvider.ExecuteQuery(querySearch);
                    return true;
                }
                catch (Exception e)
                {
                    return false;
                }
            }
            return false;

            //bool A = false;
            //string selectID = "select MAX(ID) as ID from [map_data].[dbo].[UserRoute] ";
            //int ID = 0;
            //DataTable dt = DataProvider.ExecuteQuery(selectID);
            //foreach (DataRow dr in dt.Rows)
            //{
            //    ID = Convert.ToInt32(dr["ID"]) + 1;
            //}
            //string querySearch = "insert into [map_data].[dbo].[UserRoute] values(" + ID.ToString() + "," + UserID.ToString() + "," + xS.ToString() + "," + yS.ToString() + "," + xD.ToString() + "," + yD.ToString() + ",'" + DateCreate+ "','" + lastacess + "')";
            //try
            //{
            //    DataProvider.ExecuteQuery(querySearch);
            //    return true;
            //}
            //catch (Exception e)
            //{
            //    return false;
            //}
            //return A;
        }
        public int AddUser(string Name, string Pass, string Display,string Email)
        {
            var user = new User {
                UserName = Name,
                Password = Pass,
                DisplayName = Display,
                Email = Email
            };
            // Kiem tra da ton tai user chua?
            var check = dbContext.Users.FirstOrDefault(x => x.UserName == user.UserName);
            // neu chua ton tai => them vao
            if (check == null)
            {
                dbContext.Users.InsertOnSubmit(user);
                dbContext.SubmitChanges();
                return 1;
            }
            return -1;

            //string query = "Select * from Users where UserName = '" + Name +"'";
            //DataTable dt = DataProvider.ExecuteQuery(query);
            //if (dt.Rows.Count == 1) return -1;
            
            //string querySearch = "insert into [map_data].[dbo].[Users] values(N'" + Name + "','" + Pass + "','" + Email + "',N'" + Display + "')";
            //try
            //{
            //    DataProvider.ExecuteQuery(querySearch);


            //    string selectID = "select ID from Users where UserName = '" + Name + "'";
            //    int ID = 0;
            //    dt = DataProvider.ExecuteQuery(selectID);
            //    foreach (DataRow dr in dt.Rows)
            //    {
            //        ID = Convert.ToInt32(dr["ID"]) + 1;
            //    }

            //    return ID;
            //}
            //catch (Exception e)
            //{
            //    return -1;
            //}
            //return -1;
        }
        public User KTDangNhap(string name, string pass)
        {
            return dbContext.Users.FirstOrDefault(x => x.UserName == name && x.Password == pass);
            //User temp = new User();
            //string querySearch = "select * from [map_data].[dbo].[Users] where UserName=N'"+name+"' and Password='"+pass+"'";
            //try
            //{
            //    DataTable dt= DataProvider.ExecuteQuery(querySearch);
            //    foreach (DataRow dr in dt.Rows)
            //    {
                   
            //        temp.ID = Convert.ToInt32(dr["ID"]);
            //        temp.UserName = dr["UserName"].ToString();
            //        temp.Email = dr["Email"].ToString();
            //        temp.DisplayName = dr["DisplayName"].ToString();
                    
            //    }
            //}
            //catch (Exception e)
            //{
            //    return temp;
            //}
            //return temp;
        }
        public void UpdatePass(string newpass, int ID)
        {
            // kiem tra user co ton tai chua?
            var user = dbContext.Users.FirstOrDefault(x => x.ID == ID);
            // Neu da ton tai => cho edit pass
            if (user != null)
            {
                user.Password = newpass;
                dbContext.SubmitChanges();
            }
            //bool A = false;
            //string querySearch = "update [map_data].[dbo].[Users] set Password='"+newpass+"' where ID="+ID+" and Password='"+oldpass+"'";
            //try
            //{
            //    DataProvider.ExecuteQuery(querySearch);
            //    return true;
            //}
            //catch (Exception e)
            //{
            //    return false;
            //}
            //return A;
        }
        public List<UserPlace> ReturnUserPlace(int ID)
        {
            return dbContext.UserPlaces.Where(x => x.UserID == ID).ToList();
            /*List<UserPlace> list = new List<UserPlace>();
            string querySearch = "select US.DateCreated,US.LastAcessDate,pl.Address,pl.FullName,pl.xLocation,pl.yLocation " +
                                 "from [map_data].[dbo].[UserPlace] as US,[map_data].[dbo].Place as pl " +
                                 "where US.UserID=1 and pl.ID=US.PlaceID";
            try
            {

                DataTable dt = DataProvider.ExecuteQuery(querySearch);
                foreach (DataRow dr in dt.Rows)
                {
                    UserPlace us = new UserPlace();
                    us.xLocation = Convert.ToDouble(dr["xLocation"]);
                    us.yLocation = Convert.ToDouble(dr["yLocation"]);
                    us.DateCreate =Convert.ToDateTime( dr["DateCreated"]);
                    us.LastAcess = Convert.ToDateTime(dr["LastAcessDate"]);
                    us.Address = dr["Address"].ToString();
                    us.FullName = dr["FullName"].ToString();
                    list.Add(us);
                }
                return list;
            }
            catch (Exception e)
            {
                return list;
            }*/
            
        }
        public List<UserRoute> RetrunUserRoute(int ID)
        {
            return dbContext.UserRoutes.Where(x => x.UserID == ID).ToList();
            //List<UserRoute> list = new List<UserRoute>();
            //string querySearch = "SELECT * FROM [map_data].[dbo].[UserRoute] where UserID="+ID.ToString();
            //try
            //{

            //    DataTable dt = DataProvider.ExecuteQuery(querySearch);
            //    foreach (DataRow dr in dt.Rows)
            //    {
            //        UserRoute us = new UserRoute();
            //        us.UserID = Convert.ToInt32(dr["UserID"]);
            //        us.xSource = Convert.ToDouble(dr["xSource"]);
            //        us.ySource = Convert.ToDouble(dr["ySource"]);
            //        us.xDestination = Convert.ToDouble(dr["xDestination"]);
            //        us.yDestination = Convert.ToDouble(dr["yDestination"]);
            //        us.DateCreated = Convert.ToDateTime(dr["DateCreated"]);
            //        us.LastAccessDate = Convert.ToDateTime(dr["LastAccessDate"]);
                    
            //        list.Add(us);
            //    }
            //    return list;
            //}
            //catch (Exception e)
            //{
            //    return list;
            //}
        }
    }
}